<?php
class Candidate_job_preference_model extends CI_Model {

    function __construct()
    {
        parent::__construct();
    }
    
    function get($data)
    {
    	$this->db->select('candidate_job_preference.*, job_category.name AS category, country.name AS country');
    	$this->db->from('candidate_job_preference');
    	$this->db->join('job_category', 'candidate_job_preference.idcategory = job_category.id', 'left');
    	$this->db->join('country', 'candidate_job_preference.idcountry = country.id', 'left');
    	
    	if (isset($data['idcandidate'])) {
    		$this->db->where('candidate_job_preference.idcandidate', $data['idcandidate']);
    	}
    	
    	if (isset($data['idjobpreference']) && is_numeric($data['idjobpreference'])) {
    		$this->db->where('candidate_job_preference.id', $data['idjobpreference']);
    	}
    	
    	if (isset($data['idlevel']) && is_numeric($data['idlevel'])) {
    		$this->db->where('candidate_job_preference.idlevel', $data['idlevel']);
    	}
    	
    	if (isset($data['idhierarchy']) && is_numeric($data['idhierarchy'])) {
    		$this->db->where('candidate_job_preference.idhierarchy', $data['idhierarchy']);
    	}
    	
    	if (isset($data['idcategory']) && is_numeric($data['idcategory'])) {
    		$this->db->where('candidate_job_preference.idcategory', $data['idcategory']);
    	}
    	
    	if (isset($data['idspecialty']) && is_numeric($data['idspecialty'])) {
    		$this->db->where('candidate_job_preference.idspecialty', $data['idspecialty']);
    	}
    	
    	if (isset($data['company']) && $data['company'] != '') {
    		$this->db->where('candidate_job_preference.company', $data['company']);
    	}
    	
    	if (isset($data['startdate']) && $data['startdate'] != '') {
    		$this->db->where('candidate_job_preference.startdate', $data['startdate']);
    	}
    	
   	 	if (isset($data['finishdate']) && $data['finishdate'] != '') {
    		$this->db->where('candidate_job_preference.finishdate', $data['finishdate']);
    	}
    	
    	$this->db->order_by('candidate_job_preference.id');
    	
    	return $this->db->get();
    }
    
	function insert($data)
    {
    	$this->db->trans_begin();
    	
    	$data_insert = array(
		   'idcategory' => $data['idcategory'],
		   'idcandidate' => $data['idcandidate'],
    		'idspecialty' => 'N',
    		'idstate' => 'N',
    		'idhierarchy' => 'N'
		);
		
		if (isset($data['idcountry']) && $data['idcountry'] != '')
		{
			$data_insert['idcountry'] = $data['idcountry'];
		}
		
    	if (isset($data['minsalary']) && is_numeric($data['minsalary']))
		{
			$data_insert['minsalary'] = $data['minsalary'];
		}
		
    	if (isset($data['maxsalary']) && is_numeric($data['maxsalary']))
		{
			$data_insert['maxsalary'] = $data['maxsalary'];
		}
		
		$this->db->insert('candidate_job_preference', $data_insert);
		$data['idjob'] = $this->db->insert_id();
		
		$this->insert_job_hierarchy($data);
		$this->insert_job_specialty($data);
		$this->insert_job_state($data);
		
    	if ($this->db->trans_status() === false)
		{
		    $this->db->trans_rollback();
		    return false;
		}
		else
		{
		    $this->db->trans_commit();
		    return true;
		}
    }
    
    function insert_job_specialty($data)
    {
    	$this->db->where('idjob', $data['idjob']);
    	$this->db->delete('candidate_job_specialty');
    	
    	if ($data['specialty'])
    	{
	    	$array_specialty = explode(',', $data['specialty']);
			foreach ($array_specialty as $idspecialty)
			{
				$data_insert = array(
				   'idjob' => $data['idjob'],
				   'idspecialty' => $idspecialty
				);
				
				$this->db->insert('candidate_job_specialty', $data_insert);
			}
			
			if ($array_specialty)
			{
				$this->db->where('id', $data['idjob']);
				$this->db->update('candidate_job_preference', array('idspecialty' => 'Y'));
			}
    	}
    }
    
	function insert_job_hierarchy($data)
    {
    	$this->db->where('idjob', $data['idjob']);
    	$this->db->delete('candidate_job_hierarchy');
    	
    	if ($data['hierarchy'])
    	{
	    	$array_hierarchy = explode(',', $data['hierarchy']);
			foreach ($array_hierarchy as $idhierarchy)
			{
				$data_insert = array(
				   'idjob' => $data['idjob'],
				   'idhierarchy' => $idhierarchy
				);
				
				$this->db->insert('candidate_job_hierarchy', $data_insert);
			}
			
    		if ($array_hierarchy)
			{
				$this->db->where('id', $data['idjob']);
				$this->db->update('candidate_job_preference', array('idhierarchy' => 'Y'));
			}
    	}
    }
    
	function insert_job_state($data)
    {
    	$this->db->where('idjob', $data['idjob']);
    	$this->db->delete('candidate_job_state');
    	
    	if ($data['state'])
    	{
	    	$array_state = explode(',', $data['state']);
			foreach ($array_state as $idstate)
			{
				$data_insert = array(
				   'idjob' => $data['idjob'],
				   'idstate' => $idstate
				);
				
				$this->db->insert('candidate_job_state', $data_insert);
			}
			
    		if ($array_state)
			{
				$this->db->where('id', $data['idjob']);
				$this->db->update('candidate_job_preference', array('idstate' => 'Y'));
			}
    	}
    }
    
	function update($data)
    {
    	$this->db->trans_begin();
    	
    	$data_insert = array(
		   'idcategory' => $data['idcategory'],
		   'idcandidate' => $data['idcandidate'],
    		'idspecialty' => 'N',
    		'idstate' => 'N',
    		'idhierarchy' => 'N'
		);
		
		if (isset($data['idcountry']) && $data['idcountry'] != '')
		{
			$data_insert['idcountry'] = $data['idcountry'];
		}
		
    	if (isset($data['minsalary']) && is_numeric($data['minsalary']))
		{
			$data_insert['minsalary'] = $data['minsalary'];
		}
		
    	if (isset($data['maxsalary']) && is_numeric($data['maxsalary']))
		{
			$data_insert['maxsalary'] = $data['maxsalary'];
		}
		
		$this->db->where('id', $data['idjobpreference']);
		
		$this->db->update('candidate_job_preference', $data_insert);
		$data['idjob'] = $data['idjobpreference'];
		
		$this->insert_job_hierarchy($data);
		$this->insert_job_specialty($data);
		$this->insert_job_state($data);
		
    	if ($this->db->trans_status() === false)
		{
		    $this->db->trans_rollback();
		    return false;
		}
		else
		{
		    $this->db->trans_commit();
		    return true;
		}
    }
    
	function delete($data)
    {
    	$this->db->trans_begin();
    	
    	$data_delete = array(
		   'idjob' => $data['id']
		);
		
		$this->db->delete('candidate_job_hierarchy', $data_delete);
		$this->db->delete('candidate_job_specialty', $data_delete);
		$this->db->delete('candidate_job_state', $data_delete);
    	
    	$data_delete = array(
		   'id' => $data['id']
		);
		
		$this->db->delete('candidate_job_preference', $data_delete);
		
    	if ($this->db->trans_status() === false)
		{
		    $this->db->trans_rollback();
		    return false;
		}
		else
		{
		    $this->db->trans_commit();
		    return true;
		}
    }
    
	function get_job_hierarchy($data)
    {
    	$this->db->select('candidate_job_hierarchy.*, job_hierarchy.name AS hierarchy');
    	$this->db->from('candidate_job_hierarchy');
    	$this->db->join('job_hierarchy', 'candidate_job_hierarchy.idhierarchy = job_hierarchy.id', 'left');
    	
    	if (isset($data['idjob'])) {
    		$this->db->where('candidate_job_hierarchy.idjob', $data['idjob']);
    	}
    	
    	$this->db->order_by('job_hierarchy.name');
    	
    	return $this->db->get();
    }
    
	function get_job_specialty($data)
    {
    	$this->db->select('candidate_job_specialty.*, job_specialty.name AS specialty');
    	$this->db->from('candidate_job_specialty');
    	$this->db->join('job_specialty', 'candidate_job_specialty.idspecialty = job_specialty.id', 'left');
    	
    	if (isset($data['idjob'])) {
    		$this->db->where('candidate_job_specialty.idjob', $data['idjob']);
    	}
    	
    	$this->db->order_by('job_specialty.name');
    	
    	return $this->db->get();
    }
    
	function get_job_state($data)
    {
    	$this->db->select('candidate_job_state.*, state.name AS state');
    	$this->db->from('candidate_job_state');
    	$this->db->join('state', 'candidate_job_state.idstate = state.id', 'left');
    	
    	if (isset($data['idjob'])) {
    		$this->db->where('candidate_job_state.idjob', $data['idjob']);
    	}
    	
    	$this->db->order_by('state.name');
    	
    	return $this->db->get();
    }
}
?>